# 作业：
# 1、类作业，统计镰刀和斧头的数量，要使用类方法
# class Tool():
#     count =0
#     def __init__(self,name):
#         self.name = name
#         Tool.count += 1
#     @classmethod
#     def tool_count(cls):
#         return cls.count
# class LianDao(Tool):
#     count = 0
#     def __init__(self,name):
#         self.name = name
#         LianDao.count +=1
# class FuZi(Tool):
#     count = 0
#     def __init__(self,name):
#         self.name = name
#         FuZi.count += 1
# s1 = LianDao("镰刀1")
# s2 = LianDao("镰刀2")
# s3 = LianDao("镰刀3")
# a1 = FuZi("斧头1")
# a2 = FuZi("斧头2")
# LianDao_count = LianDao.tool_count()
# FuZi_count = FuZi.tool_count()
# print(f"镰刀数量: {LianDao_count}")
# print(f"斧头数量: {FuZi_count}")
# 2、做一个学生点名系统，系统里支持：存入学生、更改学生、删除学生、点到学生姓名方法 存入学生（姓名、年龄、家庭地址、手机号）：存入成功后，
# 提示xxx存入成功,更改学生（姓名、年龄、家庭地址、手机号）：可以修改学生内容的任意一个字段,删除学生：删除的学生将不在展示
# 展示全部学生：可以看到全部的学生姓名,学生姓名：随机抽取任意一个存在的学生信息，然后打印：到！。举例： 刘闯到！
# 以上内容，需要连接数据库，每次的数据都需要增、删、改、查去做
# import pymysql
# class StudentTable:
#     def __init__(self, host, user, password, database, cursorclass=None):
#         self.host = host
#         self.user = user
#         self.password = password
#         self.database = database
#         self.cursorclass = cursorclass
#     def insert_student(self, name, age, address, tel):
#         db = pymysql.connect(host=self.host, user=self.user,
#                              password=self.password, database=self.database,
#                              cursorclass=self.cursorclass)
#         cursor = db.cursor()
#         sql = "insert into studentsystem (name, age, address, tel) VALUES (%s, %s, %s, %s)"
#         values = (name, age, address, tel)
#         cursor.execute(sql, values)
#         db.commit()
#         print(f"{name} 存入成功")
#     def del_student(self,name,age,address,tel):
#         db =pymysql.connect(host=self.host, user=self.user,
#                              password=self.password, database=self.database,
#                              cursorclass=self.cursorclass)
#         cursor = db.cursor()
#         sql = "delete "
# 
#
# StudentTable1 = StudentTable(host="182.92.193.163", user="root",
#                              password="010728", database="hanbing",
#                              cursorclass=pymysql.cursors.DictCursor)
# StudentTable1.insert_student("小花", 18, "沈阳", "1234567890")
# StudentTable1.insert_student("阿飞", "45", "沈阳", "1311111111")

# 3、新建学生成绩管理系统，随机生成100个 学生信息插入到数据库：
# 学生信息组成：学号、学生姓名、英语成绩、数学成绩、语文成绩、班级号（班级号有：1809A，1809B，1810A，1810B）
#
# 成绩划分为：优秀，良好，中等，及格，不及格。良好比中等好。
#
# 优秀：90—100之间（包括90）
# 良好：80-90
# 中等：70-80
# 及格：60-70
# 不合格：分数低于60分。
#
# 成绩管理系统提供以下方法：
# 《1》 查询所有英语成绩为优秀的、良好的、中等、及格和不合格的学生姓名和班级号
# 《2》 查询1809A，1809B，1810A，1810B每个班级的数学成绩，并且是降序返回
# 《3》 查询1809A，1809B，1810A，1810B每个班级的总人数，并且由大到小排序返回
# 《4》 查询英语、数学、语文成绩不合格的学生姓名和对应的班级号，按照不合格的人数，由大到小排序
#
# 4、定义一个图书管理系统类，图书可以新增、修改、删除，要求新增、修改、删除的图书，都要出现在数据库中
# 新增：图书名、作者名称、新建年月日
# 修改：图书名、作者名称、新建年月日
# 删除：图书删掉，且不在展示
# 查看：全量查询、精确查询、模糊查询需要用户传入形参来控制返回结果
# import pymysql
# class LibraryManageSystem:
#     def __init__(self, host, user, password, database, cursorclass=None):
#         self.host = host
#         self.user = user
#         self.password = password
#         self.database = database
#         self.cursorclass = cursorclass
#     def add_book(self,bookname,author,date):
#         db = pymysql.connect(host=self.host, user=self.user,
#                                     password=self.password, database=self.database,
#                                     cursorclass=self.cursorclass)
#         cursor = db.cursor()
#         sql = "insert into library (bookname, author, date) VALUES (%s, %s, %s)"
#         values = (bookname, author, date)
#         cursor.execute(sql,values)
#         db.commit()
#         print("图书添加成功")
#     def delete_book(self,bookname):
#         db = pymysql.connect(host=self.host, user=self.user,
#                              password=self.password, database=self.database,
#                              cursorclass=self.cursorclass)
#         cursor = db.cursor()
#         sql = "delete from library where bookname=%s"
#         values = (bookname,)
#         cursor.execute(sql, values)
#         if cursor.rowcount > 0:
#             db.commit()
#             print(f"图书 {bookname} 删除成功")
#         else:
#             print(f"未找到图书 {bookname}")
#     def update_book(self,bookname,author, date):
#         db = pymysql.connect(host=self.host, user=self.user,
#                              password=self.password, database=self.database,
#                              cursorclass=self.cursorclass)
#         cursor = db.cursor()
#         sql = "update library set bookname=%s,author=%s,date=%s where id = %s"
#         values =(id,bookname,author, date)
#         cursor.execute(sql, values)
#         if cursor.rowcount > 0:
#             db.commit()
#             print(f"图书 {bookname} 更新成功")
#         else:
#             print(f"未找到图书 {bookname}")
#     def search_all_books(self):
#         db = pymysql.connect(host=self.host, user=self.user,
#                              password=self.password, database=self.database,
#                              cursorclass=self.cursorclass)
#         cursor = db.cursor()
#         sql = "select * from library"
#         cursor.execute(sql)
#         results = cursor.fetchall()
#         if results:
#             for row in results:
#                 print(row)
#         else:
#             print("图书馆中暂无图书")
#     def search_exact_book(self, bookname):
#         db = pymysql.connect(host=self.host, user=self.user,
#                              password=self.password, database=self.database,
#                              cursorclass=self.cursorclass)
#         cursor = db.cursor()
#         sql = "select * from library where bookname=%s"
#         values = (bookname,)
#         cursor.execute(sql, values)
#         results = cursor.fetchall()
#         if results:
#             for row in results:
#                 print(row)
#         else:
#             print(f"未找到图书 {bookname}")
#     def search_fuzzy_books(self, keyword):
#         db = pymysql.connect(host=self.host, user=self.user,
#                              password=self.password, database=self.database,
#                              cursorclass=self.cursorclass)
#         cursor = db.cursor()
#         sql = "select * from library where bookname like %s or author like %s"
#         values = ('%' + keyword + '%', '%' + keyword + '%')
#         cursor.execute(sql, values)
#         results = cursor.fetchall()
#         if results:
#             for row in results:
#                 print(row)
#         else:
#             print(f"未找到包含关键字 {keyword} 的图书")
#
#
# library=LibraryManageSystem(host="182.92.193.163", user="root",
#                              password="991012", database="jyl",
#                              cursorclass=pymysql.cursors.DictCursor)
# library.add_book("小花", "健康", "20230828")
# library.add_book("jksdisfn", "jdsiewb", "20230828")
# library.delete_book("jksdisfn")
# library.update_book(1, "jksdisfn", "张三")
# library.search_all_books()
# library.search_exact_book("jksdisfn")
# library.search_fuzzy_books("jksdisfn")